﻿CREATE procedure [dbo].[sp_jiaoyi](@StartDate as datetime)
as
begin 
	declare @endDate datetime 
	select @endDate = GETDATE()
  
	if exists(select * from tempdb..sysobjects where  name  like N'%#dateTable%' and type='U')
	drop table #dateTable 

	create table #dateTable ([date] varchar(10))        

	declare @dtTemp as datetime 
	select @dtTemp = @startDate   
	while (@dtTemp <= @endDate)
	begin 
	insert into #dateTable([date]) select Convert(varchar,@dtTemp,23)
	select @dtTemp =DATEADD(day,1, @dtTemp)
	end 
	
	    select t2.[date],(t4.apptCnt+t5.billCnt) as N'番禺',(t6.apptCnt+t7.billCnt) as N'越秀',(t8.apptCnt+t9.billCnt) as N'海珠',(t2.apptCnt + t3.billCnt)as N'总交易笔数'  from 


	--appt order 
	(select a.[date],coalesce(b.apptCnt,0) as apptCnt,coalesce(b.apptMoney,0) as apptMoney from #dateTable a left join 
	(select Convert(varchar,fetchtime,23) [date],COUNT(*) as apptCnt,SUM(sumFee) as ApptMoney
	from ApptOrder where paystate =1  and  orderstatus = 21  and fetchtime between  @startDate and  @endDate 
	group by  Convert(varchar,fetchtime,23)) b 
	on a.[date] = b.[date]) t2,
	--bill order 
	(select a.[date],coalesce(b.prescMoney,0) as prescMoney,coalesce(b.yibaoMoney,0) as yibaoMoney,coalesce(b.billCnt,0) as billCnt from #dateTable a left join 
	(select Convert(varchar,fetchtime,23) [date] ,sum(PrescMoney) as prescMoney , COUNT(*) as billCnt,
	SUM(case when yibaojydjh is null then 0 else yibaoMoney end ) as yibaoMoney
	from BillOrder where PayState = 1 and OrderStatus = 8  and fetchtime between  @startDate and  @endDate   
	group by Convert(varchar,fetchtime,23) ) b 
	on a.[date] = b.[date]) t3,
	--appt order 
	(select a.[date],coalesce(b.apptCnt,0) as apptCnt,coalesce(b.apptMoney,0) as apptMoney from #dateTable a left join 
	(select Convert(varchar,fetchtime,23) [date],COUNT(*) as apptCnt,SUM(sumFee) as ApptMoney
	from ApptOrder where paystate =1  and  orderstatus = 21  and DeptName like N'%番禺%' and fetchtime between  @startDate and  @endDate 
	group by  Convert(varchar,fetchtime,23)) b 
	on a.[date] = b.[date]) t4,
	--bill order 
	(select a.[date],coalesce(b.prescMoney,0) as prescMoney,coalesce(b.yibaoMoney,0) as yibaoMoney,coalesce(b.billCnt,0) as billCnt from #dateTable a left join 
	(select Convert(varchar,fetchtime,23) [date] ,sum(PrescMoney) as prescMoney , COUNT(*) as billCnt,
	SUM(case when yibaojydjh is null then 0 else yibaoMoney end ) as yibaoMoney
	from BillOrder where PayState = 1 and OrderStatus = 8  and OrderDept like N'%番禺%' and fetchtime between  @startDate and  @endDate   
	group by Convert(varchar,fetchtime,23) ) b 
	on a.[date] = b.[date]) t5,
	--appt order 
	(select a.[date],coalesce(b.apptCnt,0) as apptCnt,coalesce(b.apptMoney,0) as apptMoney from #dateTable a left join 
	(select Convert(varchar,fetchtime,23) [date],COUNT(*) as apptCnt,SUM(sumFee) as ApptMoney
	from ApptOrder where paystate =1  and  orderstatus = 21  and DeptName like N'%越秀%' and fetchtime between  @startDate and  @endDate 
	group by  Convert(varchar,fetchtime,23)) b 
	on a.[date] = b.[date]) t6,
	--bill order 
	(select a.[date],coalesce(b.prescMoney,0) as prescMoney,coalesce(b.yibaoMoney,0) as yibaoMoney,coalesce(b.billCnt,0) as billCnt from #dateTable a left join 
	(select Convert(varchar,fetchtime,23) [date] ,sum(PrescMoney) as prescMoney , COUNT(*) as billCnt,
	SUM(case when yibaojydjh is null then 0 else yibaoMoney end ) as yibaoMoney
	from BillOrder where PayState = 1 and OrderStatus = 8  and OrderDept like N'%越秀%' and fetchtime between  @startDate and  @endDate   
	group by Convert(varchar,fetchtime,23) ) b 
	on a.[date] = b.[date]) t7,
	--appt order 
	(select a.[date],coalesce(b.apptCnt,0) as apptCnt,coalesce(b.apptMoney,0) as apptMoney from #dateTable a left join 
	(select Convert(varchar,fetchtime,23) [date],COUNT(*) as apptCnt,SUM(sumFee) as ApptMoney
	from ApptOrder where paystate =1  and  orderstatus = 21  and DeptName like N'%海珠%' and fetchtime between  @startDate and  @endDate 
	group by  Convert(varchar,fetchtime,23)) b 
	on a.[date] = b.[date]) t8,
	--bill order 
	(select a.[date],coalesce(b.prescMoney,0) as prescMoney,coalesce(b.yibaoMoney,0) as yibaoMoney,coalesce(b.billCnt,0) as billCnt from #dateTable a left join 
	(select Convert(varchar,fetchtime,23) [date] ,sum(PrescMoney) as prescMoney , COUNT(*) as billCnt,
	SUM(case when yibaojydjh is null then 0 else yibaoMoney end ) as yibaoMoney
	from BillOrder where PayState = 1 and OrderStatus = 8  and OrderDept like N'%海珠%' and fetchtime between  @startDate and  @endDate   
	group by Convert(varchar,fetchtime,23) ) b 
	on a.[date] = b.[date]) t9
	
	where t2.[date] = t3.[date] and t3.[date] = t4.[date] and t4.[date] = t5.[date] and t5.[date] = t6.[date]
	 and t6.[date] = t7.[date] and t7.[date] = t8.[date] and t8.[date] = t9.[date]
		
	
	order by 1

    --drop table 
	if exists(select * from tempdb..sysobjects where  name  like N'%#dateTable%' and type='U')
	drop table #dateTable 

end
